package com.ftg.learn.bus.mapper;

import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.baomidou.mybatisplus.extension.plugins.pagination.Page;
import com.ftg.learn.bus.VO.BusRegVo;
import com.ftg.learn.bus.VO.FundsStatisticsVo;
import org.apache.ibatis.annotations.Select;
import org.springframework.transaction.annotation.Transactional;

import java.sql.SQLException;
import java.util.List;

/**
 * <p>
 * 参数表 Mapper 接口
 * </p>
 *
 * @author lalala
 * @since 2022-03-07
 */
public interface StatisticsFundsMapper extends BaseMapper<FundsStatisticsVo> {
    /**
     * 查询用车统计
     * @return
     */
    @Select("select info.bid,info.bus_name,reg.dname,reg.dphone,reg.receiver,reg.user_name,reg.user_phone,reg.borrow_time,\n" +
            "reg.return_time,reg.start_place,reg.end_place,reg.start_mileage,reg.end_mileage \n" +
            "    from sys_bus_info info\n" +
            "    right join sys_bus_use_registration reg on info.bid = reg.bid order by info.bid")
    List<BusRegVo> showAllReg(Page<BusRegVo> pagination);


    /**
     * 查询用车统计模糊查询
     * @return
     */
    @Select("select info.bid,info.bus_name,reg.dname,reg.dphone,reg.receiver,reg.user_name,reg.user_phone,reg.borrow_time,\n" +
            "reg.return_time,reg.start_place,reg.end_place,reg.start_mileage,reg.end_mileage \n" +
            "    from sys_bus_info info\n" +
            "    right join sys_bus_use_registration reg on info.bid = reg.bid where info.bus_name like CONCAT('%',#{name},'%') order by info.bid")
    List<BusRegVo> showAllRegByName(Page<BusRegVo> pagination,String name);

    /**
     * 查询所有支出统计
     * @return
     */
    @Select("SELECT fe.bid,fe.bus_name name,\n" +
            "       SUM(IF(pid = 5, amount, 0)) qiyou,\n" +
            "       SUM(IF(pid = 6, amount, 0)) chaiyou,\n" +
            "       SUM(IF(pid = 7, amount, 0)) baoxian,\n" +
            "       SUM(IF(pid = 8, amount, 0)) weixiu,\n" +
            "       SUM(IF(pid = 9, amount, 0)) guolu,\n" +
            "       SUM(IF(pid = 10, amount, 0)) chechuans,\n" +
            "       SUM(IF(pid = 11, amount, 0)) chechuan,\n" +
            "       SUM(IF(pid = 12, amount, 0)) jiance,\n" +
            "       SUM(IF(pid = 13, amount, 0)) other,\n" +
            "       fet.total\n" +
            "FROM funds_expenditure fe\n" +
            "         left join funds_expenditure_total fet on fe.bid = fet.bid\n" +
            "GROUP BY fe.bid")
    @Transactional(rollbackFor = SQLException.class)
    List<FundsStatisticsVo> showAllFunds(Page<FundsStatisticsVo> pagination);

    /**
     * 查询所有支出统计模糊查询
     * @return
     */
    @Select("SELECT fe.bid,fe.bus_name name,\n" +
            "       SUM(IF(pid = 5, amount, 0)) qiyou,\n" +
            "       SUM(IF(pid = 6, amount, 0)) chaiyou,\n" +
            "       SUM(IF(pid = 7, amount, 0)) baoxian,\n" +
            "       SUM(IF(pid = 8, amount, 0)) weixiu,\n" +
            "       SUM(IF(pid = 9, amount, 0)) guolu,\n" +
            "       SUM(IF(pid = 10, amount, 0)) chechuans,\n" +
            "       SUM(IF(pid = 11, amount, 0)) chechuan,\n" +
            "       SUM(IF(pid = 12, amount, 0)) jiance,\n" +
            "       SUM(IF(pid = 13, amount, 0)) other,\n" +
            "       fet.total\n" +
            "FROM funds_expenditure fe\n" +
            "         left join funds_expenditure_total fet on fe.bid = fet.bid\n" +
            "GROUP BY fe.bid where fe.bus_name like CONCAT('%',#{name},'%')")
    @Transactional(rollbackFor = SQLException.class)
    List<FundsStatisticsVo> showAllFundsByName(Page<FundsStatisticsVo> pagination,String name);


}
